﻿/*
* sys_menuinfo.cs
* 2017-05-17
* 功 能： 菜单数据操作类
* 类 名： sys_menuinfo
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using FZKJ.DBUtility;
using System.Collections.Generic;//Please add references
namespace FZKJ.DAL
{
    /// <summary>
    /// 数据访问类:sys_menuinfo
    /// </summary>
    public partial class sys_menuinfo
    {
        public sys_menuinfo()
        { }

        /// <summary>
        /// 获取菜单编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxNo()
        {
            string sql = "SELECT RIGHT(1001+ISNULL(RTRIM(RIGHT(MAX(menucode),3)),0),4) from sys_menuinfo";
            return new SQLHelper().GetSingle(sql).ToString();
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(FZKJ.Model.sys_menuinfo model, List<string> btncode)
        {
            List<CommandInfo> cmd_list = new List<CommandInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into sys_menuinfo(");
            strSql.Append("pmenucode,menucode,menuname,linkaddress,menuicon,menusort,isenable,createtime,createman,updatetime,updateman)");
            strSql.Append(" values (");
            strSql.Append("@pmenucode,@menucode,@menuname,@linkaddress,@menuicon,@menusort,@isenable,@createtime,@createman,@updatetime,@updateman)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@pmenucode", SqlDbType.NVarChar,10),
                    new SqlParameter("@menucode", SqlDbType.NVarChar,10),
					new SqlParameter("@menuname", SqlDbType.NVarChar,20),
					new SqlParameter("@linkaddress", SqlDbType.NVarChar,50),
					new SqlParameter("@menuicon", SqlDbType.NVarChar,20),
					new SqlParameter("@menusort", SqlDbType.Int,4),
					new SqlParameter("@isenable", SqlDbType.Int,4),
					new SqlParameter("@createtime", SqlDbType.DateTime),
					new SqlParameter("@createman", SqlDbType.NVarChar,36),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@updateman", SqlDbType.NVarChar,36)};
            parameters[0].Value = model.pmenucode;
            parameters[1].Value = model.menucode;
            parameters[2].Value = model.menuname;
            parameters[3].Value = model.linkaddress;
            parameters[4].Value = model.menuicon;
            parameters[5].Value = model.menusort;
            parameters[6].Value = model.isenable;
            parameters[7].Value = model.createtime;
            parameters[8].Value = model.createman;
            parameters[9].Value = model.updatetime;
            parameters[10].Value = model.updateman;
            cmd_list.Add(new CommandInfo(strSql.ToString(), parameters));
            for (int i = 0; i < btncode.Count; i++)
            {
                cmd_list.Add(new CommandInfo("insert into sys_menubutton(menucode,btncode) values('" + model.menucode + "','" + btncode[i].ToString() + "')", null));
            }
            return new SQLHelper().ExecuteSqlTran(cmd_list);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(FZKJ.Model.sys_menuinfo model, List<string> btncode)
        {
            List<CommandInfo> cmd_list = new List<CommandInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update sys_menuinfo set ");
            strSql.Append("pmenucode=@pmenucode,");
            strSql.Append("menuname=@menuname,");
            strSql.Append("linkaddress=@linkaddress,");
            strSql.Append("menuicon=@menuicon,");
            strSql.Append("menusort=@menusort,");
            strSql.Append("isenable=@isenable,");
            strSql.Append("createtime=@createtime,");
            strSql.Append("createman=@createman,");
            strSql.Append("updatetime=@updatetime,");
            strSql.Append("updateman=@updateman");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@pmenucode", SqlDbType.NVarChar,10),
					new SqlParameter("@menuname", SqlDbType.NVarChar,20),
					new SqlParameter("@linkaddress", SqlDbType.NVarChar,50),
					new SqlParameter("@menuicon", SqlDbType.NVarChar,20),
					new SqlParameter("@menusort", SqlDbType.Int,4),
					new SqlParameter("@isenable", SqlDbType.Int,4),
					new SqlParameter("@createtime", SqlDbType.DateTime),
					new SqlParameter("@createman", SqlDbType.NVarChar,36),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@updateman", SqlDbType.NVarChar,36),
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@menucode", SqlDbType.NVarChar,10)};
            parameters[0].Value = model.pmenucode;
            parameters[1].Value = model.menuname;
            parameters[2].Value = model.linkaddress;
            parameters[3].Value = model.menuicon;
            parameters[4].Value = model.menusort;
            parameters[5].Value = model.isenable;
            parameters[6].Value = model.createtime;
            parameters[7].Value = model.createman;
            parameters[8].Value = model.updatetime;
            parameters[9].Value = model.updateman;
            parameters[10].Value = model.id;
            parameters[11].Value = model.menucode;
            cmd_list.Add(new CommandInfo(strSql.ToString(), parameters));
            cmd_list.Add(new CommandInfo("delete from sys_menubutton where menucode='" + model.menucode + "'", null));

            for (int i = 0; i < btncode.Count; i++)
            {
                cmd_list.Add(new CommandInfo("insert into sys_menubutton(menucode,btncode) values('" + model.menucode + "','" + btncode[i].ToString() + "')", null));
            }
            int rows = new SQLHelper().ExecuteSqlTran(cmd_list);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据菜单编号获取所属按钮
        /// </summary>
        /// <param name="menucode"></param>
        /// <returns></returns>
        public DataTable GetMenyButton(string menucode)
        {
            string t_sql = @"select a.btncode,btnicon,btnname from sys_menubutton a left join sys_buttoninfo b 
            on b.btncode=a.btncode where isenable=1 and a.menucode='"+menucode+"' order by btnsort asc";
            return new SQLHelper().Query(t_sql).Tables[0];
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from sys_menuinfo ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = new SQLHelper().ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string menucode)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from sys_menuinfo ");
            strSql.Append(" where menucode=@menucode ");
            SqlParameter[] parameters = {
					new SqlParameter("@menucode", SqlDbType.NVarChar,10)			};
            parameters[0].Value = menucode;

            int rows = new SQLHelper().ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from sys_menuinfo ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = new SQLHelper().ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public FZKJ.Model.sys_menuinfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,pmenucode,menucode,menuname,linkaddress,menuicon,menusort,isenable,createtime,createman,updatetime,updateman from sys_menuinfo ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            FZKJ.Model.sys_menuinfo model = new FZKJ.Model.sys_menuinfo();
            DataSet ds = new SQLHelper().Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public FZKJ.Model.sys_menuinfo DataRowToModel(DataRow row)
        {
            FZKJ.Model.sys_menuinfo model = new FZKJ.Model.sys_menuinfo();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["pmenucode"] != null)
                {
                    model.pmenucode = row["pmenucode"].ToString();
                }
                if (row["menucode"] != null)
                {
                    model.menucode = row["menucode"].ToString();
                }
                if (row["menuname"] != null)
                {
                    model.menuname = row["menuname"].ToString();
                }
                if (row["linkaddress"] != null)
                {
                    model.linkaddress = row["linkaddress"].ToString();
                }
                if (row["menuicon"] != null)
                {
                    model.menuicon = row["menuicon"].ToString();
                }
                if (row["menusort"] != null && row["menusort"].ToString() != "")
                {
                    model.menusort = int.Parse(row["menusort"].ToString());
                }
                if (row["isenable"] != null && row["isenable"].ToString() != "")
                {
                    model.isenable = int.Parse(row["isenable"].ToString());
                }
                if (row["createtime"] != null && row["createtime"].ToString() != "")
                {
                    model.createtime = DateTime.Parse(row["createtime"].ToString());
                }
                if (row["createman"] != null)
                {
                    model.createman = row["createman"].ToString();
                }
                if (row["updatetime"] != null && row["updatetime"].ToString() != "")
                {
                    model.updatetime = DateTime.Parse(row["updatetime"].ToString());
                }
                if (row["updateman"] != null)
                {
                    model.updateman = row["updateman"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,pmenucode,menucode,menuname,linkaddress,menuicon,menusort,isenable,createtime,createman,updatetime,updateman ");
            strSql.Append(" FROM sys_menuinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return new SQLHelper().Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,pmenucode,menucode,menuname,linkaddress,menuicon,menusort,isenable,createtime,createman,updatetime,updateman ");
            strSql.Append(" FROM sys_menuinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return new SQLHelper().Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM sys_menuinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = new SQLHelper().GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from sys_menuinfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return new SQLHelper().Query(strSql.ToString());
        }
    }
}

